package com.song.EMS.dao.impl;

import com.song.EMS.bean.Courier;
import com.song.EMS.dao.CourierDao;
import com.song.EMS.exception.CourierIDCardException;
import com.song.EMS.exception.CourierPhoneException;
import com.song.EMS.utils.DruidUtil;
import com.song.EMS.utils.TransformUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourierDaoImpl extends DruidUtil implements CourierDao {
    /**
     * 根据处理快件数排行的sql（降序/升序）
     */
    private static final String SORT_SQL = "select courierPhone,count(*) num,courierName from express e,courier c where status=0 and e.writerPhone=c.courierPhone";
    /**
     * 通过手机号修改登录时间的sql
     */
    private static final String UPDATE_LOGIN_TIME_SQL = "update courier set loginTime = ? where courierPhone = ?";
    /**
     * 查询控制台数据的sql
     */
    private static final String CONSOLE = "SELECT COUNT(courierId) total,COUNT(TO_DAYS(registerTime)=TO_DAYS(NOW()) or NULL) day  from courier";
    /**
     * 用户的新增sql
     */
    private static final String INSERT_SQL = "INSERT INTO courier VALUES(null,?,?,?,?,?,null,?)";
    /**
     * 通过手机号查询的sql
     */
    private static final String SELECTBYPHONE_SQL = "SELECT * FROM courier WHERE courierPhone = ?";
    /**
     * 查询用户所有信息sql
     */
    private static final String SELECTALL_SQL = "SELECT * FROM courier";
    /**
     * 修改用户信息sql
     */
    private static final String UPDATE_SQL = "UPDATE courier SET courierName = ?,password = ?,IDCard = ?, courierPhone = ?,numberOfDelivery = ? WHERE courierId = ?";
    /**
     * 删除用户sql
     */
    private static final String DELETE_SQL = "DELETE FROM courier WHERE courierId = ?";


    /**
     * 查询快递员的所有信息
     *
     * @param limit     true 分页
     * @param pageIndex 分页起始索引
     * @param pageSize  分页数目
     * @return 快递员信息集合
     */
    @Override
    public List<Courier> selectCouriers(Boolean limit, Integer pageIndex, Integer pageSize) {
        String sql = null;
        List<Object> list = new ArrayList<>();
        List<Courier> couriers = new ArrayList<>();
        if (limit) {
            sql = SELECTALL_SQL + " limit ?,?";
            list.add(pageIndex);
            list.add(pageSize);
        } else {
            sql = SELECTALL_SQL;
        }
        try {
            resultSet = select(sql, list);
            while (resultSet.next()) {
                Courier courier = new Courier();
                courier.setCourierId(resultSet.getInt("courierId"));
                courier.setCourierName(resultSet.getString("courierName"));
                courier.setPassword(resultSet.getString("password"));
                courier.setIDCard(resultSet.getString("IDCard"));
                courier.setCourierPhone(resultSet.getString("courierPhone"));
                courier.setNumberOfDelivery(resultSet.getInt("numberOfDelivery"));
                courier.setLoginTime(resultSet.getTimestamp("loginTime"));
                courier.setRegisterTime(resultSet.getTimestamp("registerTime"));
                couriers.add(courier);
            }
            return couriers;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return null;
    }

    /**
     * 通过手机号查询快递员
     *
     * @param courierPhone 快递员手机号
     * @return 快递员信息
     */
    @Override
    public Courier selectByPhone(String courierPhone) {
        List<Object> list = new ArrayList<>();
        Courier courier = null;
        list.add(courierPhone);
        try {
            resultSet = select(SELECTBYPHONE_SQL, list);
            while (resultSet.next()) {
                courier = new Courier();
                courier.setCourierId(resultSet.getInt("courierId"));
                courier.setCourierName(resultSet.getString("courierName"));
                courier.setPassword(resultSet.getString("password"));
                courier.setIDCard(resultSet.getString("IDCard"));
                courier.setCourierPhone(resultSet.getString("courierPhone"));
                courier.setNumberOfDelivery(resultSet.getInt("numberOfDelivery"));
                courier.setLoginTime(resultSet.getTimestamp("loginTime"));
                courier.setRegisterTime(resultSet.getTimestamp("registerTime"));
            }
            return courier;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return null;
    }

    /**
     * 快递员的录入
     *
     * @param courier 被录入的快递员对象
     */
    @Override
    public void insertCourier(Courier courier) throws CourierPhoneException, CourierIDCardException {
        try {
            List<Object> list = new ArrayList<>();
            list.add(courier.getCourierName());
            list.add(courier.getPassword());
            list.add(courier.getIDCard());
            list.add(courier.getCourierPhone());
            list.add(courier.getNumberOfDelivery());
            list.add(TransformUtil.getTimestamp());
            int update = update(INSERT_SQL, list);
        } catch (SQLException e) {
            if (e.getMessage().endsWith("key 'courier.courierPhone'")) {
                CourierPhoneException e1 = new CourierPhoneException(e.getMessage());
                throw e1;
            } else if (e.getMessage().endsWith("key 'courier.IDCard'")) {
                CourierIDCardException e2 = new CourierIDCardException(e.getMessage());
                throw e2;
            } else {
                e.printStackTrace();
            }
        } finally {
            close();
        }

    }

    /**
     * 修改快递员信息
     *
     * @param courier 被修改的快递员对象
     * @return 1 修改成功 0 失败
     */
    @Override
    public Integer updateCourier(Courier courier) throws CourierPhoneException, CourierIDCardException {
        try {
            List<Object> list = new ArrayList<>();
            list.add(courier.getCourierName());
            list.add(courier.getPassword());
            list.add(courier.getIDCard());
            list.add(courier.getCourierPhone());
            list.add(courier.getNumberOfDelivery());
            list.add(courier.getCourierId());
            int update = update(UPDATE_SQL, list);
            if (update > 0) {
                return 1;
            }
        } catch (SQLException e) {
            if (e.getMessage().endsWith("key 'courier.courierPhone'")) {
                CourierPhoneException e1 = new CourierPhoneException(e.getMessage());
                throw e1;
            } else if (e.getMessage().endsWith("key 'courier.IDCard'")) {
                CourierIDCardException e2 = new CourierIDCardException(e.getMessage());
                throw e2;
            } else {
                e.printStackTrace();
            }
        } finally {
            close();
        }
        return -1;
    }

    /**
     * 删除快递员
     *
     * @param courierId 被删除快递员的编号
     * @return true 成功  false 失败
     */
    @Override
    public Boolean deleteCourier(Integer courierId) {
        try {
            List<Object> list = new ArrayList<>();
            list.add(courierId);
            int update = update(DELETE_SQL, list);
            if (update > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return false;
    }

    /**
     * 快递员的总人数与每日注册人数
     *
     * @return 总人数与注册人数的集合
     */
    @Override
    public List courierConsole() {
        List<Object> list = new ArrayList<>();
        try {
            /*resultSet = select(COUNTALL_SQL, null);
            while(resultSet.next()){
                list.add(resultSet.getInt("total"));
            }
            resultSet = select(CONUT_SQL, null);
            while(resultSet.next()){
                list.add(resultSet.getInt("day"));
            }*/
            resultSet = select(CONSOLE, null);
            while (resultSet.next()) {
                list.add(resultSet.getInt("total"));
                list.add(resultSet.getInt("day"));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return null;
    }

    /**
     * 通过快递员手机号修改其登录记录
     *
     * @param courierPhone 快递员手机号
     */
    @Override
    public void updateLoginTime(String courierPhone) {
        try {
            List<Object> list = new ArrayList<>();
            list.add(TransformUtil.getTimestamp());
            list.add(courierPhone);
            update(UPDATE_LOGIN_TIME_SQL, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * 快递员最勤快
     *
     * @param date null表示全部时间内  %y 表示一年之内   %m  表示一个月之内
     * @return 快递员根据处理快件数排行
     */
    @Override
    public List<Courier> getHardworkingCouriers(String date) {
        StringBuilder buffer = new StringBuilder(SORT_SQL);
        List<Object> list = null;
        List<Courier> couriers = new ArrayList<>();
        if (date == null) {
            //查询全部快递员排行前三名
            buffer.append("  GROUP BY courierPhone ORDER BY num DESC LIMIT 0,3");
        } else {
            //查询当年/当月全部快递员排行前三名
            buffer.append(" and DATE_FORMAT(outputTime,?)=date_format(now(),?) GROUP BY courierPhone ORDER BY num DESC LIMIT 0,3");
            list = new ArrayList<>();
            list.add(date);
            list.add(date);
        }
        try {
            resultSet = select(buffer.toString(), list);
            while (resultSet.next()) {
                Courier courier = new Courier();
                courier.setCourierName(resultSet.getString("courierName"));
                courier.setCourierPhone(resultSet.getString("courierPhone"));
                courier.setNumberOfDelivery(resultSet.getInt("num"));
                couriers.add(courier);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return couriers;

    }

    /**
     * 快递员最勤懒
     *
     * @param date null表示全部时间内  %y 表示一年之内   %m  表示一个月之内
     * @return 快递员根据处理快件数排行
     */
    @Override
    public List<Courier> getLazyCouriers(String date) {
        StringBuilder buffer = new StringBuilder(SORT_SQL);
        List<Object> list = null;
        List<Courier> couriers = new ArrayList<>();
        if (date == null) {
            //查询全部快递员排行前三名
            buffer.append("  GROUP BY courierPhone ORDER BY num ASC LIMIT 0,3");
        } else {
            //查询当年/当月全部快递员排行前三名
            buffer.append(" and DATE_FORMAT(outputTime,?)=date_format(now(),?) GROUP BY courierPhone ORDER BY num ASC LIMIT 0,3");
            list = new ArrayList<>();
            list.add(date);
            list.add(date);
        }
        try {
            resultSet = select(buffer.toString(), list);
            while (resultSet.next()) {
                Courier courier = new Courier();
                courier.setCourierName(resultSet.getString("courierName"));
                courier.setCourierPhone(resultSet.getString("courierPhone"));
                courier.setNumberOfDelivery(resultSet.getInt("num"));
                couriers.add(courier);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return couriers;
    }
}
